import geopandas as gpd
import pandas as pd
from shapely.geometry import Point
import matplotlib.pyplot as plt

from bqtool.utils.mykit import save_json
from bqtool.utils.tools import get_db


def gis_init():
    # str = "C:\\Users\ehl0060\Desktop\新乡数据-20181122-十字星图层\新乡数据-20181122\shp格式/"
    str = "C:\\Users\ehl0060\Desktop\Xinxiang-20180820-图层数据\Xinxiang-20180820\LL\GIS_CGLK_PT.shp"
    # str = str + "SCE_LL20_PL.shp"
    # str = str + "SCE_CGLK_PT.shp"
    gf = gpd.read_file(str)
    return gf

def test():
    db = get_db('ehl_sce', 'ehl1234', '41.111.33.19')
    seg = pd.read_sql_table('t_sce_seginfo_heze', db)
    seg.drop('direction',axis=1,inplace=True)
    dir = pd.read_csv('road.csv')
    seg = pd.merge(seg,dir,on='roadsegid')
    seg.save('t_sce_seginfo', db)



if __name__ == '__main__':
    test()


    tgs = pd.read_sql_table('t_itgs_tgsinfo', get_db('ehl_analysis', 'ehl1234', '41.111.40.165', 'racdb2'))
    tgs.loc[:, 'x'] = tgs.x.astype('float')
    tgs.loc[:, 'y'] = tgs.y.astype('float')
    tgs['location'] = [Point(xy) for xy in zip(tgs['x'], tgs['y'])]
    tgs_gdf = gpd.GeoDataFrame(tgs, geometry='location')
    save_json("ll.json", tgs_gdf[['location', 'kkid', 'kkmc','cloud_id']].to_json())


    df = gis_init()
    df = df[['LKBH', 'LKMC']]
    df = df.rename(columns={'LKBH': 'CROSSINGID', 'LKMC': 'CROSSINGNAME'})
    db = get_db('ehl_sce', 'ehl1234', '41.111.33.19')
    df.save('t_sce_crossing_heze', db)

    df.geometry = df.buffer(0.2/6371.393)
    tgs_gdf.geometry = tgs_gdf.buffer(0.2/6371.393)
    for i in range(len(tgs)):
        s = tgs_gdf.loc[:,'location'][i]
        t = df.loc[df.intersects(s),'LKBH']
        if len(t)>0:
        # df.loc[t,'cloud_id'] = tgs_gdf.iloc[i].cloud_id
        # df.loc[t,'kkmc'] = tgs_gdf.iloc[i].kkmc
            tgs.loc[tgs.kkid==tgs.iloc[i].kkid,'lkid'] = t.iloc[0]
    tgs.drop('location',axis=1,inplace=True)
    tgs.save('t_itgs_tgsinfo_pass', db)
    # t_f = df.contains(tgs)


    # df = df[['DIRID', 'DIRNAME', 'QD', 'ZD','cloud_id','kkmc']]
    df = df.rename(columns={'DIRID': 'ROADSEGID', 'DIRNAME': 'ROADSEGNAME', 'QD': 'STARTCROSSID',
                            'ZD': 'ENDCROSSID'})
    df.save('t_sce_seginfo_heze', db)
    print('')
